package com.projet.erp;

import com.projet.erp.ctx.DealVO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class TableUtil {

    /**
     * 处理表
     *
     * @return
     * @throws Exception
     */
    public static void processTable(DealVO ctx, String table, String keyword) throws Exception {
        Statement ps = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        try {
            //取得执行对象
            ps = ctx.getConnection().createStatement();
            //分析表结构
            rs1 = ps.executeQuery("   select top 1 *  from  " + table);
            ResultSetMetaData rsmd = rs1.getMetaData();
            int count = rsmd.getColumnCount();
            List<String> columns = new ArrayList<>();
            List<String> columns2 = new ArrayList<>();
            String type;
            for (int i = 1; i <= count; i++) {
                type = rsmd.getColumnTypeName(i);
                if ("char".equalsIgnoreCase(type)
                        || "varchar".equalsIgnoreCase(type)
                        || "nvarchar".equalsIgnoreCase(type)
                        || "text".equalsIgnoreCase(type)) {
                    columns.add(rsmd.getColumnName(i));
                } else if ("int".equalsIgnoreCase(type)
                        || "tinyint".equalsIgnoreCase(type)
                        || "long".equalsIgnoreCase(type)
                        || "bigint".equalsIgnoreCase(type)) {
                    columns2.add(rsmd.getColumnName(i));
                    ctx.getOtherTypeSet().add(type);
                } else {
                    ctx.getOtherTypeSet().add(type);
                }
            }
            if (columns.size() <= 0) {
                return;
            }
            StringBuilder testSql = new StringBuilder();
            testSql.append(" select count(*) cc from ").append(table);
            testSql.append(" where   ");
            StringBuilder where = new StringBuilder();
            for (int i = 0; i < columns.size(); i++) {
                where.append(" or  ").append(columns.get(i)).append(" like '%").append(keyword).append("%'");
            }

            for (int i = 0; i < columns2.size(); i++) {
                where.append(" or  ").append(columns2.get(i)).append(" = ").append(keyword);
            }
            String w = where.toString().trim();
            if (w.length() > 3) {
                testSql.append(w.substring(3));
            }
            //取得执行对象
//            System.out.println(testSql.toString());
            rs2 = ps.executeQuery(testSql.toString());
            Integer tableCount;
            while (rs2.next()) {
                tableCount = rs2.getInt("cc");
                if (tableCount == null) {
                    tableCount = 0;
                }
                if (tableCount > 0) {
                    ctx.getResultSet().add(table);
                }
            }
        } finally {
            CloseUtil.deal(rs1, rs2, ps);
        }
    }


    /**
     * 查询所有表
     *
     * @param connection
     * @return
     * @throws Exception
     */
    public static List<String> queryAllTable(Connection connection) throws Exception {
        String sql = "SELECT  name AS tableName FROM sys.tables ORDER BY name";
        Statement ps = null;
        ResultSet rs = null;
        try {
            //取得执行对象
            ps = connection.createStatement();
            rs = ps.executeQuery(sql);
            List<String> tableList = new ArrayList<>();
            String tableName;
            while (rs.next()) {
                tableName = rs.getString("tableName");
                if (tableName.indexOf("$") > 0) {
                    continue;
                }
                if (tableName.startsWith("dtproperties") || "ABC".equals(tableName)) {
                    continue;
                }
                tableList.add(tableName);
            }
            return tableList;
        } finally {
            CloseUtil.deal(rs, ps);
        }
    }

    /**
     * 过滤数量
     *
     * @param connection
     * @return
     * @throws Exception
     */
    public static List<String> filter(Connection connection, List<String> inTableList, int minRow) throws Exception {
        Statement ps = null;
        ResultSet rs = null;
        List<String> tableList = new ArrayList<>();
        try {
            String countSql;
            for (String inTable : inTableList) {
                //查询源数据
                countSql = "   select count(*) cc from  " + inTable;
                //取得执行对象
                ps = connection.createStatement();
                rs = ps.executeQuery(countSql);
                Integer tableCount;
                while (rs.next()) {
                    tableCount = rs.getInt("cc");
                    if (tableCount == null) {
                        tableCount = 0;
                    }
                    if (tableCount > minRow) {
                        tableList.add(inTable);
                    }
                }
            }
        } finally {
            CloseUtil.deal(rs, ps);
        }
        return tableList;
    }

}
